package com.hkts.reportform.purchasepound.dao.imp;

import com.hkts.reportform.purchasepound.dao.PurchasePoundDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassNamePurchasePoundDaoImp
 * @Description
 * @Author yck
 * @Date2020-11-27 9:35
 * @Version V1.0
 **/
@Repository("purchasePoundDaoImp")
public class PurchasePoundDaoImp implements PurchasePoundDao {
    @Autowired
    private NamedParameterJdbcTemplate temple;
    @Autowired
    private JdbcTemplate jtemple;
    @Override
    public Integer getPoundCountByDay(String beginDate,String endDate) {
        String sql="select count(*) count from FL_RecVouch  where  dFirstDate>=:beginDate and dFirstDate<:endDate  and fSuttle<=fdue ";
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        Map<String, Object> stringObjectMap = temple.queryForMap(sql, map);
        return Integer.parseInt(stringObjectMap.get("count").toString());
    }

    @Override
    public Map<String, Object> getPoundWeightAndRate(String beginDate, String endDate) {
        String sql="select  SUM(fSuttle) fSuttle,SUM(fdue) fdue,count(*) count from FL_RecVouch  where  dFirstDate>=:beginDate and dFirstDate<:endDate and fSuttle>0  and fSuttle<=fdue ";
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        Map<String, Object> stringObjectMap = temple.queryForMap(sql, map);
        return stringObjectMap;
    }

    @Override
    public List<Map<String, Object>> purchasePoundTrend(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select count(*) count,dFirstDate from ");
        buffer.append("(select CONVERT(VARCHAR(100),dFirstDate,23) dFirstDate  from FL_RecVouch where dFirstDate>=:beginDate and CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate  and fSuttle<=fdue ) ");
        buffer.append(" a GROUP BY a.dFirstDate ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public Map<String,Object> getPurchaseCountByDate(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select count(*) count from FL_RecVouch  where  CONVERT(VARCHAR(100),dFirstDate,23)>=:beginDate and CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate  and fSuttle<=fdue  ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        Map<String, Object> stringObjectMap = temple.queryForMap(buffer.toString(), map);
        return stringObjectMap;
    }

    @Override
    public List<Map<String, Object>>  getPurchasePage(String beginDate, String endDate, int firstRow, int lastRow) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT a.*,i.cInvName  FROM  ( ");
        buffer.append("select cRVCode,cVehCode,cInvCode,fSuttle,fdue,CONVERT(VARCHAR(100),dFirstDate,20) dFirstDate,ROW_NUMBER () OVER ( ORDER BY [ID] ASC ) AS num  from FL_RecVouch  where  CONVERT(VARCHAR(100), ");
        buffer.append(" dFirstDate,23)>=:beginDate and CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate and fSuttle<=fdue ");
        buffer.append(" ) a left join Inventory i on a.cInvCode=i.cInvCode  where a.num>=:firstRow and a.num<=:lastRow ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
}
